﻿Imports DTO
Imports System.Data.OleDb

Public Class ClopDAO

    Public Function LayDanhSachLop() As List(Of CLopDTO)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strselect As String = "select MaLop,TenLop,SiSo,SiSoToiDa,MaKhoiLop from LOP"
        Dim cmd As New OleDbCommand(_strselect, cnn)
        Dim _reader As OleDbDataReader = cmd.ExecuteReader()
        Dim _danhsachlop As New List(Of CLopDTO)
        While _reader.Read()
            Dim _Lopdto As New CLopDTO()
            _Lopdto.MaLop = _reader.GetInt32(0)
            _Lopdto.TenLop = _reader.GetString(1)
            _Lopdto.SiSo = _reader.GetInt32(2)
            _Lopdto.SiSoToiDa = _reader.GetInt32(3)
            _Lopdto.MaKhoiLop = _reader.GetInt32(4)
            _danhsachlop.Add(_Lopdto)
        End While
        cnn.Close()
        Return _danhsachlop
    End Function

    Public Function LayDanhSachLopcrt() As List(Of CLopCrt)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strselect As String = "select l.MaLop,l.TenLop,l.SiSo,l.SiSoToiDa,l.MaKhoiLop,kl.TenKhoiLop from LOP l,KHOILOP kl where l.MaKhoiLop=kl.MaKhoiLop"
        Dim cmd As New OleDbCommand(_strselect, cnn)
        Dim _reader As OleDbDataReader = cmd.ExecuteReader()
        Dim _danhsachlop As New List(Of CLopCrt)
        While _reader.Read()
            Dim _Lopcrt As New CLopCrt()
            _Lopcrt.MaLop = _reader.GetInt32(0)
            _Lopcrt.TenLop = _reader.GetString(1)
            _Lopcrt.SiSo = _reader.GetInt32(2)
            _Lopcrt.SiSoToiDa = _reader.GetInt32(3)
            _Lopcrt.MaKhoiLop = _reader.GetInt32(4)
            _Lopcrt.TenKhoiLop = _reader.GetString(5)
            _danhsachlop.Add(_Lopcrt)
        End While
        cnn.Close()
        Return _danhsachlop
    End Function

    Public Function ThemLop(ByVal _lopdto As CLopDTO) As Integer
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strthemlop = "insert into LOP(TenLop,SiSO,SiSoToiDa,MaKhoiLop) values('" + _lopdto.TenLop + "',0," + _lopdto.SiSoToiDa.ToString() + "," + _lopdto.MaKhoiLop.ToString() + ")"
        Dim _cmd As New OleDbCommand(_strthemlop, cnn)
        Dim _kt As Integer = _cmd.ExecuteNonQuery()
        cnn.Close()
        Return _kt
    End Function

    Public Function CapNhatLopcrt(ByVal _lopcrt As CLopCrt) As Integer
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strcapnhat = "update LOP set TenLop='" + _lopcrt.TenLop + "',SiSo=" + _lopcrt.SiSo.ToString() + ",SiSoToiDa=" + _lopcrt.SiSoToiDa.ToString() + ",MaKhoiLop=" + _lopcrt.MaKhoiLop.ToString() + " where MaLop=" + _lopcrt.MaLop.ToString() + ""
        Dim _cmd As New OleDbCommand(_strcapnhat, cnn)
        Dim _kt As Integer = _cmd.ExecuteNonQuery()
        cnn.Close()
        Return _kt
    End Function

    Public Function CapNhatSiSo(ByVal _lopdto As CLopDTO) As Integer
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strcapnhatsiso = "update LOP set SiSo=" + _lopdto.SiSo.ToString() + " where MaLop=" + _lopdto.MaLop.ToString() + ""
        Dim _cmd As New OleDbCommand(_strcapnhatsiso, cnn)
        Dim _kt As Integer = _cmd.ExecuteNonQuery()
        cnn.Close()
        Return _kt
    End Function

    Public Function TraCuuLop(ByVal _TenLop As String) As List(Of CLopCrt)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strtracuu = "select l.MaLop,l.TenLop,l.SiSo,l.SiSoToiDa,l.MaKhoiLop,kl.TenKhoiLop from LOP l,KHOILOP kl where l.TenLop like '%" + _TenLop + "%' and l.MaKhoiLop=kl.MaKhoiLop"
        Dim _cmd As New OleDbCommand(_strtracuu, cnn)
        Dim _reader As OleDbDataReader = _cmd.ExecuteReader()
        Dim _dslop As New List(Of CLopCrt)
        While _reader.Read
            Dim _Lopcrt As New CLopCrt()
            _Lopcrt.MaLop = _reader.GetInt32(0)
            _Lopcrt.TenLop = _reader.GetString(1)
            _Lopcrt.SiSo = _reader.GetInt32(2)
            _Lopcrt.SiSoToiDa = _reader.GetInt32(3)
            _Lopcrt.MaKhoiLop = _reader.GetInt32(4)
            _Lopcrt.TenKhoiLop = _reader.GetString(5)
            _dslop.Add(_Lopcrt)
        End While
        cnn.Close()
        Return _dslop
    End Function

    Public Function DemSoLuongLop(ByVal _kldtocbb As CKhoiLopDTO) As List(Of CKhoiLopDTO)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _str = "select MaKhoiLop,count(MaKhoiLop) from LOP where MaKhoiLop=" + _kldtocbb.MaKhoiLop.ToString() + " group by MaKhoiLop"
        Dim _cmd As New OleDbCommand(_str, cnn)
        Dim _reader As OleDbDataReader = _cmd.ExecuteReader()
        Dim _ds As New List(Of CKhoiLopDTO)
        While _reader.Read()
            Dim _kldto As New CKhoiLopDTO()
            _kldto.MaKhoiLop = _reader.GetInt32(0)
            _kldto.SoLuongLop = _reader.GetInt32(1)
            _ds.Add(_kldto)
        End While
        cnn.Close()
        Return _ds
    End Function
End Class
